package vip.xiaonuo.sys.modular.timer.tasks;

import cn.hutool.log.Log;
import org.springframework.stereotype.Component;
import vip.xiaonuo.core.timer.TimerTaskRunner;

import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

import static vip.xiaonuo.sys.core.listener.ConstantsInitListener.*;

/**
 * 定时器：用来根据定时设置的时间进行数据库信息备份
 *
 * @author wang
 * @date 2024/5/15 11:41
 */
@Component
public class DBBackupRunner implements TimerTaskRunner {

    private static final Log log = Log.get();
    // 数据库连接信息
    //private static final String DB_URL = "jdbc:mysql://200.30.6.50:3306/snowy-layui-pub?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&allowMultiQueries=true&useSSL=false";
    //private static final String USER = "root";
    //private static final String PASSWORD = "123456";
    private static String DATABASE = "snowy-layui-pub";

    // 备份文件存储路径
    private static final String BACKUP_WIN_DIR = "E:/yourWinPath";
    private static final String BACKUP_LINUX_DIR = "yourLinuxPath";
    private static String BACKUP_FILE = "snow_layui_pub.sql";

    // 保留备份文件的天数
    private static final int LIMIT_LENGTH = 90;

    /**
     * 执行定时任务
     */
    @Override
    public void action() {
        backUp();
    }

    /**
     * 数据备份
     */
    public void backUp() {

        log.info("数据库备份地址：" + DATA_SOURCE_URL + "--用户名：" + DATA_SOURCE_USER_NAME + "--密码：" + DATA_SOURCE_PASSWORD);

        // 获取数据库类型（达梦 or MySQL）
        String str = DATA_SOURCE_URL.split(":")[1];
        if (str.equals("dm")) {
            DATABASE = "SNOWY";
            BACKUP_FILE = "snow_layui_pub.dmp";
        } else if (str.equals("mysql")) {
            DATABASE = "snowy-layui-pub";
            BACKUP_FILE = "snow_layui_pub.sql";
        }

        String osName = System.getProperty("os.name");
        String directoryPath;
        // 更精确的时间戳作为前缀
        String backupFileNamePrefix = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        String backupFileName = backupFileNamePrefix + "_" + BACKUP_FILE;

        if (osName.startsWith("Windows")) {
            directoryPath = BACKUP_WIN_DIR;
        } else {
            directoryPath = BACKUP_LINUX_DIR;
        }

        // 检查文件夹是否存在，如果不存在则创建
        File directory = new File(directoryPath);
        if (!directory.exists()) {
            directory.mkdirs();
        }

        // 获取文件夹中的所有文件
        File[] files = directory.listFiles();
        if (files != null && files.length >= LIMIT_LENGTH) {
            // 根据最后修改时间对文件进行排序
            Arrays.sort(files, Comparator.comparingLong(File::lastModified));

            // 删除最早的文件（即排序后的第一个文件）
            for (File file : files) {
                if (!file.isDirectory()) {
                    file.delete();
                    break; // 只删除一个文件
                }
            }
        }

        String filePath = directoryPath + File.separator + backupFileName;

        // 执行实际的备份操作
        // 记录所有表名
        List<String> tableNames = new ArrayList<>();
        // 1. 建立数据库连接
        //try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);
        try (Connection conn = DriverManager.getConnection(DATA_SOURCE_URL, DATA_SOURCE_USER_NAME, DATA_SOURCE_PASSWORD);
             BufferedWriter writer = new BufferedWriter(new FileWriter(filePath))) {
            // 2. 获取所有表名
            DatabaseMetaData metaData = conn.getMetaData();
            try (ResultSet tables = metaData.getTables(DATABASE, null, null, new String[]{"TABLE"})) {
                while (tables.next()) {
                    tableNames.add(tables.getString("TABLE_NAME"));
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            // 关闭原始ResultSet后进行备份
            for (String tableName : tableNames) {
                // 判断是否是系统表
                if (tableName.equals("##PLAN_TABLE") || tableName.equals("##HISTOGRAMS_TABLE") || tableName.equals("REG$")
                        || tableName.equals("POLICY_GROUPS") || tableName.equals("POLICY_CONTEXTS") || tableName.equals("POLICY_COLS")
                        || tableName.equals("POLICIES") || tableName.equals("DBMS_LOCK_ALLOCATED") || tableName.equals("DBMS_ALERT_INFO")
                        || tableName.equals("AQ$_QUEUE_TABLES") || tableName.equals("AQ$_QUEUES") || tableName.equals("##TMP_TBL_FOR_DBMS_LOB_CLOB")
                        || tableName.equals("##TMP_TBL_FOR_DBMS_LOB_BLOB")) {
                    continue;
                }
                backupTable(conn, writer, tableName);
            }
            log.info("数据库备份成功完成，备份文件名：" + backupFileName);

        } catch (SQLException | IOException e) {
            log.error("数据库备份失败", e);
        }
    }

    /**
     * 备份表信息
     *
     * @param conn
     * @param writer
     * @param tableName
     * @throws SQLException
     * @throws IOException
     */
    private void backupTable(Connection conn, BufferedWriter writer, String tableName) throws SQLException, IOException {
        DatabaseMetaData dbMetaData = conn.getMetaData();
        // 用于跟踪处理过的列名，避免重复
        HashSet<String> processedColumns = new HashSet<>();

        // 获取列信息
        try (ResultSet columns = dbMetaData.getColumns(DATABASE, null, tableName, null)) {
            writer.write("-- Creating table " + tableName + "\n");
            writer.write("DROP TABLE IF EXISTS " + tableName + ";\n");
            writer.write("CREATE TABLE " + tableName + " (\n");
            // 用于跟踪是否是第一个列
            boolean isFirstColumn = true;

            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                if (processedColumns.add(columnName)) {
                    if (!isFirstColumn) {
                        writer.write(",\n");
                    } else {
                        isFirstColumn = false;
                    }

                    String dataType = columns.getString("TYPE_NAME");
                    int dataSize = columns.getInt("COLUMN_SIZE");
                    int decimalDigits = columns.getInt("DECIMAL_DIGITS");
                    int nullable = columns.getInt("NULLABLE");
                    String isAutoIncrement = columns.getString("IS_AUTOINCREMENT");
                    String defaultValue = columns.getString("COLUMN_DEF");
                    String remarks = columns.getString("REMARKS");
                    writer.write(String.format("  `%s` %s", columnName, dataType));

                    // 特殊类型处理，不需要添加类型长度
                    if (dataType.equalsIgnoreCase("DATETIME") || dataType.equalsIgnoreCase("TIMESTAMP") || dataType.equalsIgnoreCase("DATE") || dataType.equalsIgnoreCase("TEXT")) {
                        if (decimalDigits > 0 && decimalDigits <= 6) {
                            writer.write("(" + decimalDigits + ")");
                        }
                    } else {
                        if (dataSize > 0) {
                            writer.write("(" + dataSize);
                            if (decimalDigits > 0) {
                                writer.write(", " + decimalDigits);
                            }
                            writer.write(")");
                        }
                    }

                    if ("YES".equalsIgnoreCase(isAutoIncrement)) {
                        writer.write(" AUTO_INCREMENT");
                    } else {
                        writer.write(nullable == DatabaseMetaData.columnNoNulls ? " NOT NULL" : " NULL");
                    }

                    if (defaultValue != null) {
                        writer.write(" DEFAULT '" + defaultValue + "'");
                    }

                    if (remarks != null && !remarks.isEmpty()) {
                        writer.write(" COMMENT '" + remarks + "'");
                    }
                }
            }

            // 处理主键
            writer.write(",\n PRIMARY KEY (`id`) USING BTREE");
            // 结束表定义
            writer.write("\n);\n\n");
        }

        // 填充表数据，这里调用一个外部定义的方法 `writeTableData`
        if (hasData(conn, tableName)) {
            writeTableData(conn, writer, tableName);
        }
    }

    /**
     * 获取表数据
     *
     * @param conn
     * @param tableName
     * @return
     * @throws SQLException
     */
    private boolean hasData(Connection conn, String tableName) throws SQLException {
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName)) {
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            return false;
        }
    }

    /**
     * 写入表数据
     *
     * @param conn
     * @param writer
     * @param tableName
     * @throws SQLException
     * @throws IOException
     */
    private void writeTableData(Connection conn, BufferedWriter writer, String tableName) throws SQLException, IOException {
        String selectQuery = "SELECT * FROM " + tableName;
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(selectQuery)) {
            ResultSetMetaData rsmd = rs.getMetaData();

            boolean firstRow = true;
            while (rs.next()) {
                if (firstRow) {
                    writer.write("INSERT INTO " + tableName + " VALUES \n");
                    firstRow = false;
                } else {
                    writer.write(",\n");
                }
                writer.write("(");
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    Object value = rs.getObject(i);
                    if (value != null) {
                        String valueString = value.toString().replace("'", "''");
                        if (value instanceof String || value instanceof Timestamp || value instanceof Date) {
                            writer.write("'" + valueString + "'");
                        } else {
                            writer.write(valueString);
                        }
                    } else {
                        writer.write("NULL");
                    }
                    if (i < rsmd.getColumnCount()) {
                        writer.write(", ");
                    }
                }
                writer.write(")");
            }
            // 如果至少写入了一行数据，才需要结束SQL语句
            if (!firstRow) {
                writer.write(";\n\n");
            }
        }
    }


}
